﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using DevExpress.XtraEditors;

namespace VietKinsNutrition.Data
{
    public class ClsTblMonan_ThucphamDAL
    {
        //ClsConnection ClsCnn = new ClsConnection();
        DataTable ReturnTbl;
        public ClsTblMonan_ThucphamDAL(DataTable tbl)
        {
            ReturnTbl = tbl;
        }
        public DataTable TblMonan_Thucpham(string Trangthai, int ListId, int Thucpham_ThucanId, int ThuvienmonanId, string ListTenthucan, string Soluong, string ListTendonvitinh)
        {
            DataTable NewTbl = ReturnTbl.Copy();
            bool Value = false;
            if (Trangthai == "1")
            {
                foreach (DataRow row in NewTbl.Rows)
                {
                    if (row["Thucpham_ThucanId"].ToString() == Thucpham_ThucanId.ToString())
                    {
                        XtraMessageBox.Show("Đã có:" + ListTenthucan + " trong thành phần lựa chọn");
                        Value = true;
                        break;
                    }
                    else
                    {
                        Value = false;
                    }
                }
                if (Value == false)
                {
                    // lấy toàn bộ ở đây;
                    //SP_ThucphambyThucpham_thucanId
                    DataTable Tbl = ThucphambyThucpham_thucanId(Thucpham_ThucanId);
                    ReturnTbl.Rows.Add(null,
                        Thucpham_ThucanId, ListTenthucan,
                        Tbl.Rows[0]["Calo"],
                        Tbl.Rows[0]["DV_Protid"], Tbl.Rows[0]["TV_Protid"],
                        Tbl.Rows[0]["DV_Lipid"],
                        Tbl.Rows[0]["TV_Lipid"],
                        Tbl.Rows[0]["GlucId"],
                        Soluong, ListTendonvitinh,
                        Tbl.Rows[0]["Tylequydoi"],
                        Tbl.Rows[0]["Tylethai"]);
                }
            }
            else
            {

                foreach (DataRow row in ReturnTbl.Rows)
                {
                    if (row["Thucpham_ThucanId"].ToString() == Thucpham_ThucanId.ToString())
                    {
                        ReturnTbl.Rows.Remove(row);
                        return ReturnTbl;
                    }
                }
            }
            return ReturnTbl;
        }
        public void Insert(int Thucpham_ThucanId, int ThuvienmonanId, string Soluong)
        {

            string NamePrc = QueryString.SPROC_TBLMONAN_THUCPHAM_ADD.Replace("@Thucpham_ThucanId", Thucpham_ThucanId.ToString()).Replace("@ThuvienmonanId", ThuvienmonanId.ToString()).Replace("@Soluong", Soluong.ToString());
            OleDbCommand Cmm = new OleDbCommand();

            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Delete(int ThuvienmonanId)
        {
            string NamePrc = QueryString.SPROC_TBLMONAN_THUCPHAM_DELETE;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@ThuvienmonanId", ThuvienmonanId));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public DataTable TV_ThucphambyThucpham_thucanId(int Thucpham_thucanId)
        {
            string NamePrc = "SELECT TV.Thucpham_ThucanId, TV.ListTenthucan, TV.Calo, TV.Tphh_Protid AS TV_Protid, TV.Tphh_Lipid AS TV_Lipid, TV.Tphh_GlucId AS GlucId, TV.ListTendonvitinh, TV.Tylequydoi, '0' as DV_Protid , '0' as DV_Lipid,  TV.Tylethai" +
 " from ( SELECT TblThucpham_thucan_1.Id AS Thucpham_ThucanId, TblThucpham_thucan_1.Tenthucan AS ListTenthucan, TblThucpham_thucan_1.Calo, TblThanhphanhoahoc_1.Tphh_Protid, TblThanhphanhoahoc_1.Tphh_Lipid, TblThanhphanhoahoc_1.Tphh_GlucId, TblDm_Donvitinh_1.Tendonvitinh AS ListTendonvitinh, TblThucpham_thucan_1.Tylequydoi, TblThucpham_thucan_1.Tylethai, TblThucpham_thucan_1.Nguondongvat " +
" FROM (TblThucpham_thucan AS TblThucpham_thucan_1 INNER JOIN TblDm_Donvitinh AS TblDm_Donvitinh_1 ON TblThucpham_thucan_1.DonvitinhId = TblDm_Donvitinh_1.Id) INNER JOIN TblThanhphanhoahoc AS TblThanhphanhoahoc_1 ON TblThucpham_thucan_1.Id = TblThanhphanhoahoc_1.IdThucpham_thucan " +
" WHERE (((TblThucpham_thucan_1.Id)=@Id) AND ((TblThucpham_thucan_1.Nguondongvat)=0))) " +
" AS TV";

            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc.Replace("@Id", Thucpham_thucanId.ToString());
            ////Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset);

            return Dset.Tables[0];
        }
        public DataTable DV_ThucphambyThucpham_thucanId(int Thucpham_thucanId)
        {
            string NamePrc = "SELECT DV.Thucpham_ThucanId, DV.ListTenthucan, DV.Calo, DV.Tphh_Protid AS DV_Protid, DV.Tphh_Lipid AS DV_Lipid, DV.Tphh_GlucId AS GlucId, DV.ListTendonvitinh, DV.Tylequydoi, '0' as TV_Protid , '0' as TV_Lipid,  DV.Tylethai" +
" FROM [SELECT     TblThucpham_thucan_1.Id AS Thucpham_ThucanId, TblThucpham_thucan_1.Tenthucan AS ListTenthucan, " +
" TblThucpham_thucan_1.Calo,                        TblThanhphanhoahoc_1.Tphh_Protid, TblThanhphanhoahoc_1.Tphh_Lipid,  " +
" TblThanhphanhoahoc_1.Tphh_GlucId,                       " +
 " TblDm_Donvitinh_1.Tendonvitinh AS ListTendonvitinh, TblThucpham_thucan_1.Tylequydoi, TblThucpham_thucan_1.Tylethai,  " +
      " TblThucpham_thucan_1.Nguondongvat  FROM      (  TblThucpham_thucan AS TblThucpham_thucan_1 INNER JOIN               " +
        " TblDm_Donvitinh AS TblDm_Donvitinh_1 ON TblThucpham_thucan_1.DonvitinhId = TblDm_Donvitinh_1.Id ) " +
" INNER JOIN                       TblThanhphanhoahoc AS TblThanhphanhoahoc_1 ON TblThucpham_thucan_1.Id = TblThanhphanhoahoc_1.IdThucpham_thucan  " +
 " WHERE     (TblThucpham_thucan_1.Id = @Id) AND (TblThucpham_thucan_1.Nguondongvat = -1)]. AS DV";

            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc.Replace("@Id", Thucpham_thucanId.ToString());
            ////Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset);

            return Dset.Tables[0];
        }

        public DataTable ThucphambyThucpham_thucanId(int Thucpham_thucanId)
        {
            DataTable Tbl = DV_ThucphambyThucpham_thucanId(Thucpham_thucanId);
            if (Tbl.Rows.Count <= 0)
            {
                Tbl = TV_ThucphambyThucpham_thucanId(Thucpham_thucanId);
            }

            return Tbl;
        }
        public DataSet SelectByThuvienmonanId(int ThuvienmonanId)
        {
            string NamePrc = QueryString.SPROC_TBLMONAN_THUCPHAM_GETBYTHUVIENMONANID.Replace("@ThuvienmonanId", ThuvienmonanId.ToString());

            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();


            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            ////Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);

            Dap.Fill(Dset);

            return Dset;

        }

    }
}
